package dao;

import pojo.Item;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ItemDAOImpl {

    private static final String TABLE_NAME = "Item";

    public static void add(Item item) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " (item_number, warehouse_number, item_name, item_position, item_size, item_type, item_quantity, item_price, item_delivery_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        JDBCUtils.execute(sql, null, item.getWarehouseNumber(), item.getItemName(), item.getItemPosition(), item.getItemSize(), item.getItemType(), item.getItemQuantity(), item.getItemPrice(), item.getItemDeliveryTime());
    }

    public static void delete(int itemNumber) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE item_number = ?";
        JDBCUtils.execute(sql, itemNumber);
    }

    public static void update(Item item) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET warehouse_number = ?, item_name = ?, item_position = ?, item_size = ?, item_type = ?, item_quantity = ?, item_price = ?, item_delivery_time = ? WHERE item_number = ?";
        JDBCUtils.execute(sql, item.getWarehouseNumber(), item.getItemName(), item.getItemPosition(), item.getItemSize(), item.getItemType(), item.getItemQuantity(), item.getItemPrice(), item.getItemDeliveryTime(), item.getItemNumber());
    }

    public static Item getById(int itemNumber) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE item_number = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, itemNumber);
        if (resultSet.next()) {
            return createItemFromResultSet(resultSet);
        }
        return null;
    }

    public static List<Item> getAll() throws SQLException {
        List<Item> items = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            Item item = createItemFromResultSet(resultSet);
            items.add(item);
        }
        return items;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<Item> getByPage(int start, int pageSize) throws SQLException {
        List<Item> items = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            Item item = createItemFromResultSet(resultSet);
            items.add(item);
        }
        return items;
    }

    public static List<Item> findByKeyword(String keyword) throws SQLException {
        List<Item> items = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE item_name LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%");
        while (resultSet.next()) {
            Item item = createItemFromResultSet(resultSet);
            items.add(item);
        }
        return items;
    }

    public static List<Item> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<Item> items = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE item_name LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            Item item = createItemFromResultSet(resultSet);
            items.add(item);
        }
        return items;
    }

    private static Item createItemFromResultSet(ResultSet resultSet) throws SQLException {
        int itemNumber = resultSet.getInt("item_number");
        int warehouseNumber = resultSet.getInt("warehouse_number");
        String itemName = resultSet.getString("item_name");
        String itemPosition = resultSet.getString("item_position");
        String itemSize = resultSet.getString("item_size");
        String itemType = resultSet.getString("item_type");
        int itemQuantity = resultSet.getInt("item_quantity");
        double itemPrice = resultSet.getDouble("item_price");
        java.sql.Timestamp itemDeliveryTime = resultSet.getTimestamp("item_delivery_time");
        return new Item(itemNumber, warehouseNumber, itemName, itemPosition, itemSize, itemType, itemQuantity, itemPrice, itemDeliveryTime);
    }
}
